
package anni.tools;

import java.io.*;
import java.util.*;
import jxl.*;

@SuppressWarnings("unchecked")
public class GenHsqldb {
    static String inputFileName = null;
    static String outputFileName = null;
    static String prefix = null;

    static List foreignKey = new ArrayList();

    /** * 根据命令行参数进行初始化. */
    static void init(String[] args) throws Exception {
        outputFileName = args[0];
        inputFileName = args[1];
        prefix = args[2];

        File file = new File(outputFileName);
        file.delete();
        file.createNewFile();
    }

    /** * 生成清空表结构的脚本. */
    static void genDrop(List<Bean> beanList) throws Exception {
        PrintWriter out = new PrintWriter(new FileOutputStream(outputFileName, true));
        for (Bean bean : beanList) {
            out.println();
            out.println("drop table " + prefix + bean.tableName + " if exists;");
        }
        out.flush();
        out.close();
    }

    /** * 生成建表脚本. */
    static void genTable(Bean bean) throws Exception {
        String tableName = bean.tableName;
        System.out.println(" -- start gen table : " + prefix + tableName);

        int rows = bean.rowList.size();
        if (rows > 1) {
            PrintWriter out = new PrintWriter(new FileOutputStream(outputFileName, true));
            out.println();
            out.println("create table " + prefix + tableName + " (");

            StringBuffer buff = new StringBuffer();

            // primary keys
            List pks = new ArrayList();
            // foreign keys
            Map fks = new HashMap();
            for (Row row : bean.rowList) {
                if ("".equals(row.name)) {
                    continue;
                }
                buff.append("    ").append(row.name).append(" ").append(row.type);
                if ("not null".equals(row.notNull)) {
                    buff.append(" not null");
                }
                if (!"".equals(row.defaultValue)) {
                    buff.append(" default ").append(row.defaultValue);
                }
                if (!"".equals(row.pk)) {
                    if (row.pk.equals("increment")) {
                        buff.append(" generated by default as identity(start with 1, increment by 1)");
                    }
                    pks.add(row.name);
                }
                if (!"".equals(row.fk)) {
                    fks.put(row.name, row.fk);
                }
                buff.append(",\r\n");
            }

            // pks
            if (!pks.isEmpty()) {
                buff.append("    constraint pk_").append(tableName).append(" primary key(");
                for (int j = 0; j < pks.size(); j++) {
                    buff.append(pks.get(j)).append(",");
                }
                buff.deleteCharAt(buff.length() - 1);
                buff.append("),\r\n");
            }

            // fks
            if (!fks.isEmpty()) {
                for (Iterator iter = fks.entrySet().iterator(); iter.hasNext(); ) {
                    Map.Entry entry = (Map.Entry) iter.next();
                    String column =  (String) entry.getKey();
                    String foreignkey = (String) entry.getValue();
                    buff.append("    constraint fk_").append(tableName).append("_").append(column)
                        .append(" foreign key(").append(column).append(") references ")
                        .append(prefix).append(foreignkey).append("(id),\r\n");
                }
            }

            buff.delete(buff.length() - 3, buff.length());
            out.println(buff);
            out.println(");");
            out.println();
            out.flush();
            out.close();
        }
        System.out.println(rows);

        System.out.println(" -- end gen table : " + prefix + tableName);
    }

    /** * 生成外键. */
    static void genForeignKey() throws Exception {
        PrintWriter out = new PrintWriter(new FileOutputStream(outputFileName, true));

        // foreign key
        for (int i = 0; i < foreignKey.size(); i++) {
            String line = (String) foreignKey.get(i);
            String[] tmp = line.split(":");

            out.println("alter table " + prefix + tmp[0]);
            out.println("  add constraint fk_" + tmp[0] + "_" + tmp[1]);
            out.println("  foreign key (" + tmp[1] + ")");
            out.println("  references " + prefix + tmp[2] + ";");
            out.println();
        }
        out.flush();
        out.close();
    }

    /** * main. */
    public static void main(String[] args) throws Exception {
        System.out.println("start...");
        init(args);

        List<Bean> beanList = ExcelUtils.read(inputFileName);

        genDrop(beanList);

        for (Bean bean : beanList) {
            genTable(bean);
        }

        genForeignKey();

        System.out.println("end...");
    }
}
